package com.turing.sensivitycode.utils;

import com.csvreader.CsvReader;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class ExcelUtil {

	private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    public static List<List<String>> readExcel(String excelFilePath) {
        List<List<String>> result = new ArrayList<>();
        if (excelFilePath.endsWith(".xlsx") || excelFilePath.endsWith(".xls")) {
            result = handleData(excelFilePath);
        }
        return result;
    }

    // 读取后缀是xlsx的excel表格
    public static List<List<String>> handleData(String excelFilePath) {
        List<List<String>> result = new ArrayList<>();
        try (InputStream inputStream = new FileInputStream(excelFilePath);
             Workbook xssfWorkbook = WorkbookFactory.create(inputStream)) {
            //循环每一页，并处理当前的循环页
            for (Sheet sheet : xssfWorkbook) {
                if (sheet == null) {
                    continue;
                }

                for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                    //Row表示每一行的数据
                    Row row = sheet.getRow(rowNum);
                    if (null != row) {
                        int minColIx = row.getFirstCellNum();
                        int maxColIx = row.getLastCellNum();
                        List<String> rowList = new ArrayList<>();
                        //遍历该行，并获取每一个cell的数据
                        for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                            Cell cell = row.getCell(colIx);
                            if (cell == null) {
                                continue;
                            }
                            rowList.add(cell.toString());
                        }
                        result.add(rowList);
                    }
                }
            }
        } catch (IOException | InvalidFormatException e) {
			logger.error("出现异常！",e);
        }
        return result;
    }

    // BufferedReader 读取csv文件
    public static ArrayList<String> readCsvByBufferedReader(String filePath) {
        File csv = new File(filePath);
        csv.setReadable(true);
        csv.setWritable(true);
        InputStreamReader isr = null;
        BufferedReader br = null;
        try {
            isr = new InputStreamReader(new FileInputStream(csv), "UTF-8");
            br = new BufferedReader(isr);
        } catch (Exception e) {
            e.printStackTrace();
        }
        String line = "";
        ArrayList<String> records = new ArrayList<>();
        try {
            while ((line = br.readLine()) != null) {
                records.add(line);
            }
            System.out.println("csv表格读取行数：" + records.size());
        } catch (IOException e) {
            e.printStackTrace();
        }
        return records;
    }

    // CsvReader方式读取csv文件
    public static ArrayList<String> readCsvByCsvReader(String filePath) {
        ArrayList<String> strList = null;
        try {
            ArrayList<String[]> arrList = new ArrayList<String[]>();
            strList = new ArrayList<String>();
            CsvReader reader = new CsvReader(filePath, ',', Charset.forName("UTF-8"));
            while (reader.readRecord()) {
                System.out.println(Arrays.asList(reader.getValues()));
                arrList.add(reader.getValues()); // 按行读取，并把每一行的数据添加到list集合
            }
            reader.close();
            // System.out.println("读取的行数：" + arrList.size());
            for (int row = 0; row < arrList.size(); row++) {
                // 组装String字符串
                String ele = arrList.get(row)[0] + "," + arrList.get(row)[1] + "," + arrList.get(row)[2];
                strList.add(ele);

            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return strList;
    }

    public static void main(String[] args) {
        String path1 = "D:\\file\\tmp\\hello.xlsx";
        List<List<String>> list1 = readExcel(path1);
        System.out.println(list1);

//        String path2 = "src/main/java/com/aliyun/db/web/InstanceInfo2.xlsx";
//        List<List<String>> list2 = readExcel(path2);
//        System.out.println(list2);

//        String path3 = "src/main/java/com/aliyun/db/web/InstanceInfo3.csv";
//        List<String> list3 = readCsvByBufferedReader(path3);
//        System.out.println(list3);
//
//        List<String> list4 = readCsvByCsvReader(path3);
//        System.out.println(list4);
    }
}
